package org.flown.util;

import java.util.ArrayList;
import java.util.HashMap;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.jdbc.support.rowset.SqlRowSet;


/**
 * 	<bean id="SpringJDBCUtil" class="org.flown.util.SpringJDBCUtil">
 * 		<property name="JDBCTemplate" ref="JDBCTemplate">
 * 	</bean>
 * 
 * 	<bean id="SpringJDBCUtil" class="org.flown.util.SpringJDBCUtil">
 * 		<property name="DataSource" ref="DataSource">
 * 	</bean>
 * @author weifei(flown)
*/
public class SpringJDBCUtil
{
	private static JdbcTemplate JDBC_TEMPLATE;

	public SpringJDBCUtil(){}
	public SpringJDBCUtil(JdbcTemplate jdbc_tempalte)
	{
		this.setJDBCTemplate(jdbc_tempalte);
	}
	
	public void setJDBCTemplate(JdbcTemplate jdbc_tempalte)
	{
		SpringJDBCUtil.init(jdbc_tempalte);
	}
	public void setDataSource(DataSource data_source)
	{
		SpringJDBCUtil.init(data_source);
	}
	

	public static void init(JdbcTemplate jdbc_tempalte)
	{
		SpringJDBCUtil.JDBC_TEMPLATE = jdbc_tempalte;
	}
	public static void init(DataSource data_source)
	{
		SpringJDBCUtil.JDBC_TEMPLATE = new JdbcTemplate(data_source);
	}


	public static void execute(String sql)
	{
		if (sql == null || sql.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".sql:\n\tsql is null or equals \"\"");
			return;
		}
		SpringJDBCUtil.JDBC_TEMPLATE.execute(sql);
	}


	public static void update(String sql)
	{
		if (sql == null || sql.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".sql:\n\tsql is null or equals \"\"");
			return;
		}
		SpringJDBCUtil.JDBC_TEMPLATE.update(sql);
	}


	public static SqlRowSet query(String sql)
	{
		SqlRowSet set = SpringJDBCUtil.JDBC_TEMPLATE.queryForRowSet(sql);
		if (!set.last())
			return null;
		return set;
	}
	
	
	public static <T> T queryForObject(String table_name, Class<T> require_class, String condition)
	{
		String sql = "select * from " + table_name + " where " + condition + ";";
		RowMapper<T> mapper = new SingleColumnRowMapper<>(require_class);
		return SpringJDBCUtil.JDBC_TEMPLATE.queryForObject(sql, mapper);
	}
	

	public static <T> T queryForObject(String table_name, Class<T> require_class, String... conditions)
	{
		StringBuilder sql_conditions = new StringBuilder();

		for (String condition : conditions)
		{
			sql_conditions.append(" and ").append(condition);
		}
		sql_conditions.delete(0, 4);

		String sql = "select * from " + table_name + " where " + sql_conditions.toString() + ";";
		RowMapper<T> mapper = new SingleColumnRowMapper<>(require_class);
		return SpringJDBCUtil.JDBC_TEMPLATE.queryForObject(sql, mapper);
	}


	public static <T> T queryForObject(String table_name, Class<T> require_class, HashMap<String, String> conditions)
	{
		StringBuilder sql_conditions = new StringBuilder();

		for (String key : conditions.keySet())
		{
			sql_conditions.append(" and ");
			sql_conditions.append(key);
			sql_conditions.append("='");
			sql_conditions.append(conditions.get(key));
			sql_conditions.append("'");
		}
		sql_conditions.delete(0, 4);

		String sql = "select * from " + table_name + " where " + sql_conditions.toString() + ";";
		RowMapper<T> mapper = new SingleColumnRowMapper<>(require_class);
		return SpringJDBCUtil.JDBC_TEMPLATE.queryForObject(sql, mapper);
	}

	
	private static String getUpdateSQL(String table_name, HashMap<String, String>key_value)
	{
		/*
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".getUpdateSQL:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (key_value == null || key_value.isEmpty())
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".getUpdateSQL:\n\tthe key_value is null or empty");
			return null;
		}
		*/
		
		StringBuilder sql_set = new StringBuilder();
		for (String key : key_value.keySet())
		{
			sql_set.append(key).append("='").append(key_value.get(key)).append("',");
		}
		sql_set.deleteCharAt(sql_set.length() - 1);
		
		return "update " + table_name + " set " + sql_set.toString() + ";";
	}
	
	/**
	 * select from table
	 * @return 返回ResultSet；若 columns_name 为空，则返回整个表的ResultSet
	 * <br>ResultSet 不为null则可用，内容为空会返回null
	 * */
	public static SqlRowSet select(String table_name, String... columns_name)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".select:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (columns_name == null || columns_name.length == 0)
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".select:\n\tthe columns_name is null or whose length is 0");
			return null;
		}
		
		StringBuilder sql_columns_name = new StringBuilder();
		
		if (columns_name == null || columns_name.length == 0)
			sql_columns_name.append(" * ");
		else
		{
			for (String name : columns_name)
			{
				sql_columns_name.append(name).append(",");
			}
			sql_columns_name.deleteCharAt(sql_columns_name.length() - 1);
		}
		
		String sql = "select " + sql_columns_name + " from " + table_name + ";";
		return SpringJDBCUtil.query(sql);
	}
	
	
	/**
	 * select一行
	 * @param table_name 表名
	 * @param conditions key='value'
	 * @return 返回ResultSet,内容只有一行
	 * <br>ResultSet 不为null则可用，内容为空会返回null
	 * */
	public static SqlRowSet selectRow(String table_name, String... conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".selectRow:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (conditions == null || conditions.length == 0)
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".selectRow:\n\tthe conditions is null or whose length is 0");
			return null;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String condition : conditions)
		{
			sql_conditions.append(" and ").append(condition);
		}
		sql_conditions.delete(0, 4);
		
		String sql = "select * from " + table_name + " where " + sql_conditions + " limit 1;";
		return SpringJDBCUtil.query(sql);
	}
	
	
	/**
	 * select一行
	 * @return 返回ResultSet,内容只有一行
	 * <br>ResultSet 不为null则可用，内容为空会返回null
	 * */
	public static SqlRowSet selectRow(String table_name, HashMap<String, String> conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".selectRow:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (conditions == null || conditions.isEmpty())
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".selectRow:\n\tthe conditions is null or empty");
			return null;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String key : conditions.keySet())
		{
			sql_conditions.append(" and ").append(key).append("='").append(conditions.get(key)).append("'");
		}
		sql_conditions.delete(0, 4);
		
		String sql = "select * from " + table_name + " where " + sql_conditions + " limit 1;";
		
		return SpringJDBCUtil.query(sql);
	}
	

	/**
	 * select某列某行的值
	 * @param conditions key='value'
	 * @return 返回value
	 * */
	public static Object selectValue(String table_name, String column_name, String... conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".selectValue:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (conditions == null || conditions.length == 0)
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".selectValue:\n\tthe conditions is null or whose length is 0");
			return null;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String condition : conditions)
		{
			sql_conditions.append(" and ").append(condition);
		}
		sql_conditions.delete(0, 4);
		
		String sql = "select " + column_name + " from " + table_name + " where " + sql_conditions + ";";
		SqlRowSet set = SpringJDBCUtil.query(sql);
		if (set == null)
			return null;

		/*
		if (!set.next())
			return null;
		*/
		
		// 在
		// JDBCUtil.statementExecuteQuery(sql); 
		// 里头已经检查过并且 set.last() 了
		return set.getObject(0);
	}
	
	
	/**
	 * select某列某行的值
	 * @return 返回value
	 * */
	public static Object selectValue(String table_name, String column_name, HashMap<String, String> conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".selectValue:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (conditions == null || conditions.isEmpty())
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".selectValue:\n\tthe conditions is null or empty");
			return null;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String key : conditions.keySet())
		{
			sql_conditions.append(" and ").append(key).append("='").append(conditions.get(key)).append("'");
		}
		sql_conditions.delete(0, 4);
		
		String sql = "select " + column_name + " from " + table_name + " where " + sql_conditions + ";";
		SqlRowSet set = SpringJDBCUtil.query(sql);
		if (set == null)
			return null;
		
		/*
		if (!set.next())
			return null;
		*/
		// 在
		// JDBCUtil.statementExecuteQuery(sql); 
		// 里头已经检查过并且 set.last() 了
		return set.getObject(1);
	}
	
	
	/**
	 * count()
	 * 计数，计算某列的行数
	 * @return 返回计数；若 columns_name 为空，则返回整个表所有列的行数
	 * */
	public static int countRow(String table_name, String column_name)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".countRow:\n\ttable_name is null or equals \"\"");
			return 0;
		}
		if (column_name == null || column_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".countRow:\n\tthe column_name is null or equals \"\"");
			return 0;
		}
		
		String sql = "select count("
				+ column_name
				+ ") as count from "
				+ table_name
				+ ";";
		
		SqlRowSet set = SpringJDBCUtil.query(sql);
		if (set == null)
			return 0;
		
		//if(!set.next())
		
		// 在
		// JDBCUtil.statementExecuteQuery(sql); 
		// 里头已经检查过并且 set.last() 了
		return set.getInt(1);
	}
	
	
	/**
	 * count()
	 * 计数，计算某列的行数
	 * @return 返回计数；若 columns_name 为空，则返回整个表所有列的行数
	 * */
	public static Integer[] countRow(String table_name, String... columns_name)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".countRow:\n\ttable_name is null or equals \"\"");
			return null;
		}
		if (columns_name == null || columns_name.length == 0)
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".countRow:\n\tthe columns_name is null or whose length is 0");
			return null;
		}
		
		StringBuilder sql_columns_name = new StringBuilder();
		for (String name : columns_name)
		{
			sql_columns_name.append(name).append(",");
		}
		sql_columns_name.deleteCharAt(sql_columns_name.length() - 1);
		
		String sql = "select count("
				+ sql_columns_name.toString()
				+ ") as count from "
				+ table_name
				+ ";";
		
		SqlRowSet set = SpringJDBCUtil.query(sql);
		if (set == null)
			return null;
		
		ArrayList<Integer> list_count = new ArrayList<Integer>();
		
		// JDBCUtil.statementExecuteQuery(sql);
		// 已经调用过 set.last()
		while(!set.next())
		{
			list_count.add(set.getInt(1));
		}
		
		if (list_count.size() == 0)
			return null;
		return (Integer[])list_count.toArray();
	}
	
	
	/**
	 * insert行
	 * 设置所有value
	 * */
	public static void insert(String table_name, String... values)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".insert:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (values == null || values.length == 0)
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".insert:\n\tthe values is null or whose length is 0");
			return;
		}
		
		StringBuilder sql_values = new StringBuilder();
		for (String value : values)
		{
			sql_values.append("'").append(value).append("',");
		}
		sql_values.deleteCharAt(sql_values.length() - 1);
		
		String sql = "insert into "
				+ table_name
				+ " values("
				+ sql_values
				+ ");";
		
		SpringJDBCUtil.execute(sql);
	}
	
	
	/**
	 * insert行
	 * 设置部分value
	 * @param map HashMap<列名, 值>
	 * */
	public static void insert(String table_name, HashMap<String, String> map)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".insert:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (map == null || map.isEmpty())
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".insert:\n\tthe key_value is null or empty");
			return;
		}
		
		StringBuilder sql_columns_name = new StringBuilder();
		StringBuilder sql_values = new StringBuilder();
		
		for (String key : map.keySet())
		{
			sql_columns_name.append(key).append(",");
			sql_values.append("'").append(map.get(key)).append("',");
		}
		sql_columns_name.deleteCharAt(sql_columns_name.length() - 1);
		sql_values.deleteCharAt(sql_values.length() - 1);
		
		String sql = "insert into "
				+ table_name
				+ " ("
				+ sql_columns_name.toString()
				+ ") values ("
				+ sql_values.toString()
				+ ");";
		
		SpringJDBCUtil.execute(sql);
	}
	

	/**
	 * @param map HashMap<列名, 值>
	 * @param conditions key='value'
	 * */
	public static void update(String table_name, HashMap<String, String> map, String... conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".update:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (map == null || map.isEmpty())
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".update:\n\tthe key_value is null or empty");
			return;
		}
		if (conditions == null || conditions.length == 0)
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".update:\n\tthe conditions is null or whose length is 0");
			return;
		}
		
		StringBuilder sql = new StringBuilder(SpringJDBCUtil.getUpdateSQL(table_name, map));
		sql.deleteCharAt(sql.length() - 1);
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String condition : conditions)
		{
			sql_conditions.append(" and ").append(condition);
		}
		sql_conditions.delete(0, 4);
		sql.append(" where ").append(sql_conditions).append(";");
	
		
		SpringJDBCUtil.update(sql.toString());
	}
	
	
	/**
	 * update某行中的某些列
	 * @param map HashMap<列名, 值>
	 * @param conditions HashMap<列名, 值>
	 * */
	public static void update(String table_name, HashMap<String, String> map, HashMap<String, String> conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".update:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (map == null || map.isEmpty())
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".update:\n\tthe key_value is null or empty");
			return;
		}
		if (conditions == null || conditions.isEmpty())
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".update:\n\tthe conditions is null or empty");
			return;
		}
		
		StringBuilder sql = new StringBuilder(SpringJDBCUtil.getUpdateSQL(table_name, map));
		sql.deleteCharAt(sql.length() - 1);
		
		StringBuilder sql_conditions = new StringBuilder();
		for (String column_name : conditions.keySet())
		{
			sql_conditions.append(" and ").append(column_name).append("='").append(conditions.get(column_name)).append("'");
		}
		sql_conditions.delete(0, 4);
		sql.append(" where ").append(sql_conditions).append(";");
	
	
		SpringJDBCUtil.update(sql.toString());
	}
	
	
	/**
	 * update某行中的一列
	 * */
	public static void updateOneValue(String table_name, String column_name, String value, String condition)
	{
		String sql = "update "
				+ table_name
				+ " set "
				+ column_name
				+ "='"
				+ value
				+ "' where "
				+ condition
				+ ";";
		SpringJDBCUtil.update(sql);
	}
	
	
	/**
	 * 删除表中的行(row)
	 * @param conditions key='value'
	 * */
	public static void delete(String table_name, String... conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".delete:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (conditions == null || conditions.length == 0)
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".delete:\n\tthe conditions is null or whose length is 0");
			return;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		
		for (String condition : conditions)
		{
			sql_conditions.append(" and ").append(condition);
		}
		sql_conditions.delete(0, 4);
		
		String sql = "delete from " + table_name + " where " + sql_conditions.toString() + ";";
		SpringJDBCUtil.execute(sql);
	}
	
	
	/**
	 * 删除表中的行(row)
	 * @param conditions HashMap<列名, 值>
	 * */
	public static void delete(String table_name, HashMap<String, String> conditions)
	{
		if (table_name == null || table_name.equals(""))
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".delete:\n\ttable_name is null or equals \"\"");
			return;
		}
		if (conditions == null || conditions.isEmpty())
		{
			System.out.println(SpringJDBCUtil.class.toString() + ".delete:\n\tthe conditions is null or empty");
			return;
		}
		
		StringBuilder sql_conditions = new StringBuilder();
		
		for (String key : conditions.keySet())
		{
			sql_conditions.append(" and ").append(key).append("='").append(conditions.get(key)).append("'");
		}
		sql_conditions.delete(0, 4);
		
		String sql = "delete from " + table_name + " where " + sql_conditions.toString() + ";";
		SpringJDBCUtil.execute(sql);
	}
}






